package finelProject.listeners;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import finelProject.MyAppConstants;

public class InitializeDB implements ServletContextListener{

	public InitializeDB() {
		// TODO Auto-generated constructor stub
	}
	//utility that checks whether the customer tables already exists
    private boolean tableAlreadyExists(SQLException e) {
        boolean exists;
        if(e.getSQLState().equals("X0Y32")) {
            exists = true;
        } else {
            exists = false;
        }
        return exists;
    }
	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void contextInitialized(ServletContextEvent event) {
		ServletContext cntx = event.getServletContext();
		try{
			//obtain CustomerDB data source from Tomcat's context
    		Context context = new InitialContext();
    		BasicDataSource ds = (BasicDataSource)context.lookup(MyAppConstants.DB_DATASOURCE);
    		Connection conn = ds.getConnection();
    		/** This will try to create the users table in the DB**/
    		boolean created = false;
    		try{
    			
    			Statement stmt = conn.createStatement();
    			stmt.executeUpdate(MyAppConstants.CREATE_USERS_TABLE);
    			//commit update
        		conn.commit();
        		stmt.close();
    		}catch (SQLException e){
    			//check if exception thrown since table was already created (so we created the database already 
    			//in the past
    			created = tableAlreadyExists(e);
    			if (!created){
    				throw e;//re-throw the exception so it will be caught in the
    				//external try..catch and recorded as error in the log
    			}
    		}
    		/** This will try to create the messages table in the DB**/
    		 created = false;
    		try{
    			
    			Statement stmt = conn.createStatement();
    			stmt.executeUpdate(MyAppConstants.CREATE_MESSAGES_TABLE);
    			//commit update
        		conn.commit();
        		stmt.close();
    		}catch (SQLException e){
    			//check if exception thrown since table was already created (so we created the database already 
    			//in the past
    			created = tableAlreadyExists(e);
    			if (!created){
    				throw e;//re-throw the exception so it will be caught in the
    				//external try..catch and recorded as error in the log
    			}
    		}
    		/** This will try to create the Follow table in the DB**/
   		 created = false;
   		try{
   			
   			Statement stmt = conn.createStatement();
   			stmt.executeUpdate(MyAppConstants.CREATE_FOLLOW_TABLE);
   			//commit update
       		conn.commit();
       		stmt.close();
   		}catch (SQLException e){
   			//check if exception thrown since table was already created (so we created the database already 
   			//in the past
   			created = tableAlreadyExists(e);
   			if (!created){
   				throw e;//re-throw the exception so it will be caught in the
   				//external try..catch and recorded as error in the log
   			}
   		}
		}
		catch (SQLException | NamingException e){
			//log error 
    		cntx.log("Error during database initialization",e);
		}
	}

}

